package com.model;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.servlet.ServletException;

public  class StructuredContextDataModel extends DataModel {
	
	
	public StructuredContextDataModel(String datasource,String url,String username,String password,String driverName)
	{
		this.datasource=datasource;
		this.url=url;
		this.username=username;
		this.password=password;
		this.driverName=driverName;
	}
	
	public  int getImageFrequency(String contextvariable,String contextvalue,int imageID) throws Exception
	{
		ResultSet rs;
		int freq=0;
		try {
		rs=fetch("select sum(Frequency) as Frequency from rank where ImageID='"+imageID+"' and "+contextvariable+"='"+contextvalue+"'");
		} catch (ServletException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		throw e;
		}
		
		while(rs.next())
		{
			freq=rs.getInt("Frequency");
		}
		return freq;
		
	}
	
	
	public  long getImageID(String URL) throws Exception{
		long imageID=0;
		ResultSet rSet=null;
		String sql="select ImageID from Image where ImageURL='"+URL+"'";
		try{
			rSet=fetch(sql);
			if(rSet!=null)
			{
				while(rSet.next())
				{
					imageID=rSet.getInt("ImageID");
				}
			}
			
		}catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return imageID ;
	} 
	
	
	public Map<Integer, Double> GetContextVector(String ContextDimension, String ContextValue) throws Exception {
		Map<Integer, Double> ContextVector = new HashMap<Integer, Double>();
		ResultSet Rset = null;
		
		
		try {
			
			String SqlQuery = "SELECT ImageID FROM searchhistory WHERE " + ContextDimension + "='" + ContextValue + "'";
			Rset=fetch(SqlQuery);
			while (Rset.next()) {
				String imageID = Rset.getString("ImageID");
	            Integer freq = getImageFrequency(ContextDimension, ContextValue, Integer.parseInt(imageID));
	            ContextVector.put(Integer.parseInt(imageID), (double)freq);	            
			}
			
		} catch (Exception exp) {
			
			System.out.println("SQLException:"+exp.getMessage());
			throw exp;
	       
		}	
		
		return ContextVector;
	}	 
	

	
	protected  long getImageCount() throws Exception
	{
		ResultSet rSet=null;
		long count=0;
		String sql="select MAX(ImageID) from Image";
		try{
			rSet=fetch(sql);
			if(rSet!=null)
			{
				while(rSet.next())
				{
					count=rSet.getInt("MAX(ImageID)");
				}
			}
			
		}catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		
		return count;	
	}
	
	protected  long getReqCount() throws Exception
	{
		ResultSet rSet=null;
		long count=0;
		String sql="select MAX(RequestID) from searchhistory";
		try{
			rSet=fetch(sql);
			if(rSet!=null)
			{
				while(rSet.next())
				{
					count=rSet.getInt("MAX(RequestID)");
				}
			}
			
		}catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		
		return count;
	}

	@Override
	public int saveUserFeedback(HashMap contextMap,long userID,String keyword, String url,long imageId) throws Exception
	{
		if(url.equals(""))
		{
			return saveUserFeedback(contextMap, userID, keyword, imageId);
		}
		else
		{
			return saveUserFeedback(contextMap, userID, keyword, url);
		}
	
	}
	
	private int saveUserFeedback(HashMap contextMap,long userID,String keyword, String url) throws Exception
	{
		long imageID=getImageID(url);
		long newImageID=0;
		if(imageID==0)
		{
			newImageID=getImageCount()+1;
		}
		else
		{
			newImageID=imageID;
		}
		long reqID=getReqCount()+1;
		
		String sql_saveToImage=null;
		String sql_getFromRankings="select Frequency from rank where ";
		String sql_saveToRankings_part1="insert into rank (";
		String sql_saveToRankings_part2=" values (";
		String sql_saveToRankings=null;
		String sql_updateRankings="update rank set Frequency=";
		String sql_updateRankings_part2=" where ";
		String sql_saveToHistory_part1=null;
		String sql_saveToHistory_part2=null;
		
		if(userID==Integer.MIN_VALUE)
		{
			sql_saveToHistory_part1="insert into searchhistory (RequestID,Keyword,ImageID,";
	        sql_saveToHistory_part2=" values ('"+reqID+"','"+keyword+"','"+newImageID+"',";
	        
		}
		else
		{
			sql_saveToHistory_part1="insert into searchhistory (RequestID,UserID,Keyword,ImageID,";
	        sql_saveToHistory_part2=" values ('"+reqID+"','"+userID+"','"+keyword+"','"+newImageID+"',";
	        
			
		}
		String sql_saveToHistory=null;              

        Iterator<String> iter = contextVariables.iterator();
        while (iter.hasNext()) {
                
        	String tempConVar = iter.next();
        	if(tempConVar.equals("Keyword"))continue;
        	else if(tempConVar.equals("UserID"))continue;
        	else
        	{
        		
        	if(iter.hasNext())
                {
                sql_saveToHistory_part1 += tempConVar + ",";
                sql_saveToHistory_part2 += "'"+contextMap.get(tempConVar.toLowerCase())+"',";
                
                sql_saveToRankings_part1 += tempConVar+ ",";
                sql_saveToRankings_part2 += "'"+contextMap.get(tempConVar.toLowerCase())+"',";
                
                sql_getFromRankings += tempConVar+"='"+contextMap.get(tempConVar.toLowerCase())+"' and ";
                sql_updateRankings_part2 += tempConVar+"='"+contextMap.get(tempConVar.toLowerCase())+"' and ";
                }
            else
                {
                sql_saveToHistory_part1 += tempConVar+")";
                sql_saveToHistory_part2 +="'"+ contextMap.get(tempConVar.toLowerCase())+"')";
                
                sql_saveToRankings_part1 += tempConVar+",Frequency,ImageID)";
                sql_saveToRankings_part2 +="'"+ contextMap.get(tempConVar.toLowerCase())+"',1,'"+newImageID+"')";
                
                sql_getFromRankings += tempConVar+"='"+contextMap.get(tempConVar.toLowerCase())+"' and ImageID="+newImageID+"";
                sql_updateRankings_part2 += tempConVar+"='"+contextMap.get(tempConVar.toLowerCase())+"' and ImageID="+newImageID+"";
                
                
                }  
        	}
        }
        
        sql_saveToHistory = sql_saveToHistory_part1+sql_saveToHistory_part2;
       
        sql_saveToRankings=sql_saveToRankings_part1+sql_saveToRankings_part2;
        
               
        if(imageID==0)
		{
			try {
				
				sql_saveToImage="Insert into Image (ImageID,ImageURL) values('"+newImageID+"','"+url+"')";
				save(sql_saveToImage);
				save(sql_saveToHistory);
			} catch (Exception e) {
				e.printStackTrace();
				throw e;
			}
			
		}
		else
		{
			try {
				save(sql_saveToHistory);
			} catch (ServletException e) {
				
				e.printStackTrace();
				throw e;
			}
		}
        
        ResultSet frequency=fetch(sql_getFromRankings);
      
        	int i=1;
        	boolean updated=false;
        	
        	while(frequency.next())
        	{
        		
        		i=Integer.parseInt(frequency.getString("Frequency"));
        		i=i+1;
        		sql_updateRankings="update rank set Frequency='"+i+"'"+sql_updateRankings_part2;
            	save(sql_updateRankings);
            	updated=true;
        	}
        	if(!updated)
        	{
        		save(sql_saveToRankings);
        	}
        	
     		
		return 1;
	}
	
	private int saveUserFeedback(HashMap contextMap,long userID,String keyword, Long imageId) throws Exception
	{
		
		long reqID=getReqCount()+1;
		
		String sql_saveToImage=null;
		String sql_getFromRankings="select Frequency from rank where ";
		String sql_saveToRankings_part1="insert into rank (";
		String sql_saveToRankings_part2=" values (";
		String sql_saveToRankings=null;
		String sql_updateRankings="update rank set Frequency=";
		String sql_updateRankings_part2=" where ";
		String sql_saveToHistory_part1=null;
		String sql_saveToHistory_part2=null;
		
		if(userID==Integer.MIN_VALUE)
		{
			sql_saveToHistory_part1="insert into searchhistory (RequestID,Keyword,ImageID,";
	        sql_saveToHistory_part2=" values ('"+reqID+"','"+keyword+"','"+imageId+"',";
	        
		}
		else
		{
			sql_saveToHistory_part1="insert into searchhistory (RequestID,UserID,Keyword,ImageID,";
	        sql_saveToHistory_part2=" values ('"+reqID+"','"+userID+"','"+keyword+"','"+imageId+"',";
	        
			
		}
		String sql_saveToHistory=null;              

        Iterator<String> iter = contextVariables.iterator();
        while (iter.hasNext()) {
                
        	String tempConVar = iter.next();
        	if(tempConVar.equals("Keyword"))continue;
        	else if(tempConVar.equals("UserID"))continue;
        	else
        	{
        		
        	if(iter.hasNext())
                {
                sql_saveToHistory_part1 += tempConVar + ",";
                sql_saveToHistory_part2 += "'"+contextMap.get(tempConVar.toLowerCase())+"',";
                
                sql_saveToRankings_part1 += tempConVar+ ",";
                sql_saveToRankings_part2 += "'"+contextMap.get(tempConVar.toLowerCase())+"',";
                
                sql_getFromRankings += tempConVar+"='"+contextMap.get(tempConVar.toLowerCase())+"' and ";
                sql_updateRankings_part2 += tempConVar+"='"+contextMap.get(tempConVar.toLowerCase())+"' and ";
                }
            else
                {
                sql_saveToHistory_part1 += tempConVar+")";
                sql_saveToHistory_part2 +="'"+ contextMap.get(tempConVar.toLowerCase())+"')";
                
                sql_saveToRankings_part1 += tempConVar+",Frequency,ImageID)";
                sql_saveToRankings_part2 +="'"+ contextMap.get(tempConVar.toLowerCase())+"',1,'"+imageId+"')";
                
                sql_getFromRankings += tempConVar+"='"+contextMap.get(tempConVar.toLowerCase())+"' and ImageID="+imageId+"";
                sql_updateRankings_part2 += tempConVar+"='"+contextMap.get(tempConVar.toLowerCase())+"' and ImageID="+imageId+"";
                
                
                }  
        	}
        }
        
        sql_saveToHistory = sql_saveToHistory_part1+sql_saveToHistory_part2;
       
        sql_saveToRankings=sql_saveToRankings_part1+sql_saveToRankings_part2;
        
               
    
		try {
			save(sql_saveToHistory);
		} catch (ServletException e) {

			e.printStackTrace();
			throw e;
		}
		
        
        ResultSet frequency=fetch(sql_getFromRankings);
      
        	int i=1;
        	boolean updated=false;
        	
        	while(frequency.next())
        	{
        		
        		i=Integer.parseInt(frequency.getString("Frequency"));
        		i=i+1;
        		sql_updateRankings="update rank set Frequency='"+i+"'"+sql_updateRankings_part2;
            	save(sql_updateRankings);
            	updated=true;
        	}
        	if(!updated)
        	{
        		save(sql_saveToRankings);
        	}
        	
        	
      	return 1;
	}

}
